前言

今天要來練習SQL的查詢語法

使用的軟體是SQLite Studio

我上的課程是郭耀仁老師在台大資訊系統訓練班所開設的『SQL資料分析』

這篇文章會使用老師上課教到的觀念

利用kaggle上的資料實際操作

會分成三個部分，分別是：

• 函數的介紹
• 通用函數
• 聚合函數

使用的資料是kaggle上一筆五百人的身高體重紀錄

500 Person Gender-Height-Weight-Body Mass Index
先來看看前十筆資料長怎樣

SELECT *
 FROM data
 LIMIT 10;

另外一筆資料是netflix上的紀錄

https://www.kaggle.com/sarahjeeeze/imdbfile

也來看看資料長怎樣

SELECT *
 FROM record
 LIMIT 10;

函數類型

函數可依照功能分為兩類：

• 通用函數
  • 轉換資料類型。
  • 計算數值。
  • 操作文字。
  • 操作日期時間。
• 聚合函數

通用函數與聚合函數的不同在於其所作用的維度

• 通用函數作用會顯示在水平方向。

  像是每個觀測值會對應到一個輸出

  假如現在想讓身高變成公尺的形式

SELECT *,
       Height*0.01 AS height_in_meters
 FROM data
 LIMIT 10;

可以看到最右邊那欄的將左邊的身高一一輸出成公尺的形式

• 聚合函數整合在垂直方向

  就是整欄變數最後會對應到一個輸出

  SELECT AVG(Height) AS avg_height
   FROM data;
  

可以通過聚合函數計算整體的平均身高

通用函數

舉例來說，要計算每個人的BMI值

BMI = 體重(公斤) / 身高^2(公尺)^2

SELECT *,
      Weight/((Height*0.01)*(Height*0.01))AS BMI
 FROM data
 LIMIT 10;

可以看到出來的BMI小數點位數很多

若想要限制輸出的小數點位數

ROUND 函數: 調整查詢結果的小數點位數

ROUND(REAL, n_digits)

假如只想要取小數點後三位

SELECT *,
      ROUND(Weight/((Height*0.01)*(Height*0.01)),3)AS BMI
 FROM data
 LIMIT 10;

可以看到最後指出來小數點後三位

COALESCE ：將遺漏值轉換為指定常數

COALESCE(NULL, replacement)

舉例來說，在 Netflix 的 record 資料表中有許多director沒有被記錄到

再看一次這筆資料前十筆的紀錄
可以看到director的欄位內有很多空格

利用COALESCE來替換指定的常數

LENGTH :計算文字中有幾個字元，包含空格、標點符號

LENGTH(TEXT)

假設現在想知道每個title的文字長度

SELECT title,
       LENGTH(title) AS length_of_title
 FROM record
 LIMIT 10;

LOWER 與 UPPER : 調整英文的大小寫

LOWER(TEXT)
UPPER(TEXT)

SELECT title,
       UPPER(title) AS upper_title,
       LOWER(title) AS lower_title
 FROM record
 LIMIT 10;

聚合函數

常見的聚合函數

• AVG(column_name)：變數平均數
• COUNT(column_name)：計算變數的「非」遺漏值數
• COUNT(*)：計算觀測值數
• MAX(column_name)：最大值
• MIN(column_name)：最小值
• SUM(column_name)：計算變數的加總

計算平均身高和體重

SELECT AVG(Height) AS avg_height,
       AVG(Weight) AS avg_weight
 FROM data
 LIMIT 10;

找出身高最高和最矮的

SELECT MAX(Height) AS highest,
       MIN(Height) AS shortest
 FROM data
 LIMIT 10;